unit clsNFOperacoesTributos;

interface

uses uDMCI, DbTables, ubiblioCI, sysutils;

type
  TNFOperacoesTributos = class(TObject)
  private

    pID_OPERACAO,
    pID_NCM,
    pID_IMPOSTO : Integer;
    pSIGLA_ESTADO : String;
  public
    vID_TRIBUTACAO : Double;
    vCFOP, vTP_IMPOSTO : String;

    function Gravar(pID_OPERACAO,pID_NCM,pID_IMPOSTO: Integer; pSIGLA_ESTADO : String): Byte;
    function Excluir(P_ID_CODIGO: Integer): Byte;
    procedure Consultar(var p_q: TQuery);
    function RetornaMaiorCodigo: Integer;
    procedure Consultar2(var p_q: TQuery; P_DESCRICAO: string);
    procedure ConsultarCST(var p_q: TQuery);
    procedure Consultar3(var p_q: TTable);
    procedure ConsultarICMS(var p_q: TQuery);
    procedure ConsultarCOFINS(var p_q: TQuery);
    procedure ConsultarIPI(var p_q: TQuery);
    procedure ConsultarISSQN(var p_q: TQuery);
    procedure ConsultarPIS(var p_q: TQuery);
    procedure ConsultarNCM(var p_q: TQuery);
    function GravarNCM(pID_OPERACAO, pID_NCM, pID_IMPOSTO: Integer): Byte;
    function CopiarNCMUF(vID_OPERACAO, vID_NCM, vID_IMPOSTO: Integer; vEstado: string): Boolean;
    function CopiarNCM(vID_OPERACAO, vID_NCM : Integer): Boolean;
  end;

implementation

{ TCodFiscal }

procedure TNFOperacoesTributos.Consultar(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT O.NFNCMEST_ID_OPERACAO AS ID_OPERACAO, M.MNCM_CODIGO AS ID_NCM, O.NFNCMEST_ID_IMPOSTO AS ID_TRIBUTACAO, '+
              '        O.NFNCMEST_CFOP AS CFOP, O.NFNCMEST_SIGLA_ESTADO AS SIGLA_ESTADO, '+
              '        O.NFNCMEST_TIPO_IMPOSTO AS TIPO_IMPOSTO,  M.MNCM_EXTIPI, '+
              '        M.DESCRICAO AS ClasfiscalNCM, I.DESCRICAO AS DescricaoImposto, I.ALIQUOTA, I.BASEDECALCULO AS BC, '+
              '        I.PCREDICMS, I.PDIF, O.NFNCMEST_ID_NCM AS CODIGONCM'+
              ' FROM NF_OPERACOES_NCMPORESTADO O JOIN MERCADORIA_NCM M ON (O.NFNCMEST_ID_NCM = M.MNCM_ID_NCM) '+
              '                                  JOIN IMPOSTOS I ON (O.NFNCMEST_ID_IMPOSTO = I.ID_CODIGO)');
end;

procedure TNFOperacoesTributos.ConsultarNCM(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT O.NFNCM_ID_OPERACAO AS ID_OPERACAO, M.MNCM_CODIGO AS ID_NCM, M.MNCM_EXTIPI, '+
              '        O.NFNCM_ID_TRIBUTACAO AS ID_TRIBUTACAO, O.NFNCM_CFOP AS CFOP, O.NFNCM_CFOP AS SIGLA_ESTADO, '+
              '        O.NFNCM_TIPO_IMPOSTO AS TIPO_IMPOSTO, M.DESCRICAO AS ClasfiscalNCM, I.DESCRICAO AS DescricaoImposto, '+
              '        I.ALIQUOTA, I.BASEDECALCULO AS BC, O.NFNCM_ID_NCM AS CODIGONCM '+
              ' FROM NF_OPERACOES_PORNCM O JOIN MERCADORIA_NCM M ON (O.NFNCM_ID_NCM = M.MNCM_ID_NCM) '+
              '                            JOIN IMPOSTOS I ON (O.NFNCM_ID_TRIBUTACAO = I.ID_CODIGO)');
end;

procedure TNFOperacoesTributos.ConsultarICMS(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT ID_CODIGO,DESCRICAO,BASEDECALCULO,ALIQUOTA,MENSAGEM,SETORNAIMPRESSORA, '+
              '        IMP_BC_MODAL,IMP_ID_IMPOSTO,IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,MODBC, '+
              '        ID_CODIGO||DESCRICAO AS Descri FROM IMPOSTOS WHERE IMP_ID_IMPOSTO = 0 ORDER BY DESCRICAO');
end;

procedure TNFOperacoesTributos.ConsultarIPI(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT ID_CODIGO,DESCRICAO,BASEDECALCULO,ALIQUOTA,MENSAGEM,SETORNAIMPRESSORA, '+
              '        IMP_BC_MODAL,IMP_ID_IMPOSTO,IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,MODBC, '+
              '        ID_CODIGO||DESCRICAO AS Descri FROM IMPOSTOS WHERE IMP_ID_IMPOSTO = 1 ORDER BY DESCRICAO');
end;

procedure TNFOperacoesTributos.ConsultarPIS(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT ID_CODIGO,DESCRICAO,BASEDECALCULO,ALIQUOTA,MENSAGEM,SETORNAIMPRESSORA, '+
              '        IMP_BC_MODAL,IMP_ID_IMPOSTO,IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,MODBC, '+
              '        ID_CODIGO||DESCRICAO AS Descri FROM IMPOSTOS WHERE IMP_ID_IMPOSTO = 2 ORDER BY DESCRICAO');
end;

procedure TNFOperacoesTributos.ConsultarCOFINS(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT ID_CODIGO,DESCRICAO,BASEDECALCULO,ALIQUOTA,MENSAGEM,SETORNAIMPRESSORA, '+
              '        IMP_BC_MODAL,IMP_ID_IMPOSTO,IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,MODBC, '+
              '        ID_CODIGO||DESCRICAO AS Descri FROM IMPOSTOS WHERE IMP_ID_IMPOSTO = 3 ORDER BY DESCRICAO');
end;

procedure TNFOperacoesTributos.ConsultarISSQN(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT ID_CODIGO,DESCRICAO,BASEDECALCULO,ALIQUOTA,MENSAGEM,SETORNAIMPRESSORA, '+
              '        IMP_BC_MODAL,IMP_ID_IMPOSTO,IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,MODBC, '+
              '        ID_CODIGO||DESCRICAO AS Descri FROM IMPOSTOS WHERE IMP_ID_IMPOSTO = 4 ORDER BY DESCRICAO');
end;

procedure TNFOperacoesTributos.Consultar3(var p_q: TTable);
begin
  p_q.Filter := '';
  p_q.Filter := ' SELECT * FROM IMPOSTOS ORDER BY DESCRICAO ';
end;

procedure TNFOperacoesTributos.ConsultarCST(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM CODIGOST ORDER BY CST ');
end;

procedure TNFOperacoesTributos.Consultar2(var p_q: TQuery; P_DESCRICAO: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT * FROM IMPOSTOS WHERE DESCRICAO LIKE (:P_DESCRICAO) ORDER BY DESCRICAO');
  p_q.parambyname('P_DESCRICAO').asstring := P_DESCRICAO;
end;

function TNFOperacoesTributos.Excluir(P_ID_CODIGO: Integer): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM IMPOSTOS WHERE ID_CODIGO= :P_ID_CODIGO');
  tq.parambyname('P_ID_CODIGO').AsInteger := P_ID_CODIGO;
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

function TNFOperacoesTributos.Gravar(pID_OPERACAO,pID_NCM,pID_IMPOSTO: Integer; pSIGLA_ESTADO : String): Byte;

var
  tq, tq1: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add(' SELECT * FROM NF_OPERACOES_NCMPORESTADO WHERE NFNCMEST_ID_NCM = :P_ID_NCM ');
  tq.parambyname('P_ID_NCM').AsInteger      := pID_NCM;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO NF_OPERACOES_NCMPORESTADO ( '+
                '             NFNCMEST_ID_OPERACAO, '+
                '             NFNCMEST_ID_NCM, '+
                '             NFNCMEST_ID_IMPOSTO, '+
                '             NFNCMEST_CFOP, '+
                '             NFNCMEST_SIGLA_ESTADO, '+
                '             NFNCMEST_TIPO_IMPOSTO) '+
                ' VALUES(:P_ID_OPERACAO, '+
                '         :P_ID_NCM, '+
                '         :P_ID_IMPOSTO, '+
                '         :P_CFOP, '+
                '         :P_SIGLA_ESTADO, '+
                '         :P_TP_IMPOSTO) ');

  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE NF_OPERACOES_NCMPORESTADO SET NFNCMEST_CFOP = :P_CFOP, '+
                '            NFNCMEST_ID_IMPOSTO = :P_ID_IMPOSTO '+
                ' WHERE NFNCMEST_ID_OPERACAO = :P_ID_OPERACAO AND '+
                '       NFNCMEST_ID_NCM = :P_ID_NCM AND '+
                '       NFNCMEST_TIPO_IMPOSTO = :P_TP_IMPOSTO AND '+
                '       NFNCMEST_SIGLA_ESTADO = :P_SIGLA_ESTADO ');
  end;
  tq1.parambyname('P_ID_OPERACAO').AsFloat   := pID_OPERACAO;
  tq1.parambyname('P_ID_NCM').AsFloat        := pID_NCM;
  tq1.parambyname('P_ID_IMPOSTO').AsFloat    := pID_IMPOSTO;
  tq1.parambyname('P_CFOP').AsString         := vCFOP;
  tq1.parambyname('P_SIGLA_ESTADO').AsString := pSIGLA_ESTADO;
  tq1.parambyname('P_TP_IMPOSTO').AsString   := vTP_IMPOSTO;

  tq1.execsql;
  tq1.close;
  tq1.Free;
  result := 1;
  tq.close;
  tq.Free;
end;

function TNFOperacoesTributos.GravarNCM(pID_OPERACAO,pID_NCM,pID_IMPOSTO: Integer): Byte;

var
  tq, tq1: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add(' SELECT * FROM NF_OPERACOES_PORNCM WHERE NFNCM_ID_NCM = :P_ID_NCM ');
  tq.parambyname('P_ID_NCM').AsInteger      := pID_NCM;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO NF_OPERACOES_PORNCM ( '+
                '             NFNCM_ID_OPERACAO, '+
                '             NFNCM_ID_NCM, '+
                '             ID_IMPOSTO, '+
                '             NFNCM_ID_TRIBUTACAO, '+
                '             NFNCM_CFOP, '+
                '             NFNCM_TIPO_IMPOSTO) '+
                ' VALUES(:P_ID_OPERACAO, '+
                '         :P_ID_NCM, '+
                '         :P_ID_IMPOSTO, '+
                '         :P_ID_TRIBUTACAO, '+
                '         :P_CFOP, '+
                '         :P_TP_IMPOSTO) ');

  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE NF_OPERACOES_PORNCM SET NFNCM_CFOP = :P_CFOP, '+
                '            NFNCM_TIPO_IMPOSTO = :P_TP_IMPOSTO, '+
                '            NFNCM_ID_TRIBUTACAO = :P_ID_TRIBUTACAO '+
                ' WHERE NFNCM_ID_OPERACAO = :P_ID_OPERACAO AND '+
                '       NFNCM_ID_NCM = :P_ID_NCM AND '+
                '       NFNCM_TIPO_IMPOSTO = :P_TP_IMPOSTO ');
  end;
  tq1.parambyname('P_ID_OPERACAO').AsFloat   := pID_OPERACAO;
  tq1.parambyname('P_ID_NCM').AsFloat        := pID_NCM;
  tq1.parambyname('P_ID_TRIBUTACAO').AsFloat := vID_TRIBUTACAO;
  tq1.parambyname('P_CFOP').AsString         := vCFOP;
  tq1.parambyname('P_TP_IMPOSTO').AsString   := vTP_IMPOSTO;

  tq1.execsql;
  tq1.close;
  tq1.Free;
  result := 1;
  tq.close;
  tq.Free;
end;

function TNFOperacoesTributos.RetornaMaiorCodigo(): Integer;
var
  tq1: TQuery;
begin
  tq1 := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active := False;
  tq1.close;
  tq1.sql.Clear;
  tq1.params.clear;
  tq1.sql.add(' SELECT MAX(ID_CODIGO) AS ID_CODIGO FROM IMPOSTOS ');
  tq1.Open;
  if trim(tq1.FieldByName('ID_CODIGO').AsString) = '' then
    Result := 0
  else
    Result := tq1.FieldByName('ID_CODIGO').AsInteger;
  tq1.close;
  tq1.Free;
end;

function TNFOperacoesTributos.CopiarNCMUF(vID_OPERACAO, vID_NCM, vID_IMPOSTO : Integer; vEstado : string): Boolean;
var
  TQ, tq1: TQuery;
  aID_NCM : Integer;
begin
  try
    TQ := Tquery.Create(DMCI);
    TQ.databasename := DMCI.dbCentralImpostos.databasename;
    TQ.active := False;

    TQ.close;
    TQ.sql.clear;
    TQ.params.clear;
    TQ.sql.add('SELECT MNCM_ID_NCM FROM MERCADORIA_NCM WHERE MNCM_CODIGO = :P_NCM_CODIGO');
    TQ.parambyname('P_NCM_CODIGO').AsFloat := vID_NCM;
    TQ.Open;
    if not TQ.IsEmpty then begin
      aID_NCM := TQ.FieldByName('MNCM_ID_NCM').AsInteger;
      tq1 := Tquery.Create(DMCI);
      tq1.databasename := DMCI.dbCentralImpostos.databasename;
      tq1.active := False;

      tq1.close;
      tq1.sql.clear;
      tq1.params.clear;
      tq1.sql.add(' UPDATE NF_OPERACOES_NCMPORESTADO SET NFNCMEST_CFOP = :P_CFOP, '+
                  '            NFNCMEST_ID_IMPOSTO = :P_ID_IMPOSTO '+
                  ' WHERE NFNCMEST_ID_OPERACAO = :P_ID_OPERACAO AND '+
                  '       NFNCMEST_ID_NCM = :P_ID_NCM AND '+
                  '       NFNCMEST_TIPO_IMPOSTO = :P_TP_IMPOSTO AND '+
                  '       NFNCMEST_SIGLA_ESTADO = :P_SIGLA_ESTADO ');

      tq1.parambyname('P_ID_OPERACAO').AsFloat   := vID_OPERACAO;
      tq1.parambyname('P_ID_NCM').AsFloat        := aID_NCM;
      tq1.parambyname('P_ID_IMPOSTO').AsFloat    := vID_IMPOSTO;
      tq1.parambyname('P_CFOP').AsString         := vCFOP;
      tq1.parambyname('P_SIGLA_ESTADO').AsString := vEstado;
      tq1.parambyname('P_TP_IMPOSTO').AsString   := vTP_IMPOSTO;

      tq1.execsql;
      tq1.close;
      tq1.Free;
      result := True;
    end else
      result := False;
    TQ.close;
    TQ.Free;
  except
    result := False;
  end;
end;


function TNFOperacoesTributos.CopiarNCM(vID_OPERACAO, vID_NCM : Integer): Boolean;
var
  TQ, tq1: TQuery;
  aID_NCM : Integer;
begin
  try
    TQ := Tquery.Create(DMCI);
    TQ.databasename := DMCI.dbCentralImpostos.databasename;
    TQ.active := False;

    TQ.close;
    TQ.sql.clear;
    TQ.params.clear;
    TQ.sql.add('SELECT MNCM_ID_NCM FROM MERCADORIA_NCM WHERE MNCM_CODIGO = :P_NCM_CODIGO');
    TQ.parambyname('P_NCM_CODIGO').AsFloat := vID_NCM;
    TQ.Open;
    if not TQ.IsEmpty then begin
      aID_NCM := TQ.FieldByName('MNCM_ID_NCM').AsInteger;
      tq1 := Tquery.Create(DMCI);
      tq1.databasename := DMCI.dbCentralImpostos.databasename;
      tq1.active := False;

      tq1.close;
      tq1.sql.clear;
      tq1.params.clear;
      tq1.sql.add(' UPDATE NF_OPERACOES_PORNCM SET '+
                  '        NFNCM_CFOP = :P_CFOP, '+
                  '        NFNCM_ID_TRIBUTACAO = :P_ID_TRIBUTACAO '+
                  ' WHERE NFNCM_ID_OPERACAO = :P_ID_OPERACAO AND '+
                  '       NFNCM_ID_NCM = :P_ID_NCM AND '+
                  '       NFNCM_TIPO_IMPOSTO = :P_TP_IMPOSTO ');

      tq1.parambyname('P_ID_OPERACAO').AsFloat   := vID_OPERACAO;
      tq1.parambyname('P_ID_NCM').AsFloat        := aID_NCM;
      tq1.parambyname('P_ID_TRIBUTACAO').AsFloat := vID_TRIBUTACAO;
      tq1.parambyname('P_CFOP').AsString         := vCFOP;
      tq1.parambyname('P_TP_IMPOSTO').AsString   := vTP_IMPOSTO;

      tq1.execsql;
      tq1.close;
      tq1.Free;
      result := True;
    end else
      result := False;
    TQ.close;
    TQ.Free;
  except
    result := False;
  end;
end;

end.




